package com.mixmer.excelanalysis.excel;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.Objects;

public class ExcelAnalysisUtil {
    private static XSSFWorkbook workbook;

    public static Workbook getWorkbook(String excelFilePath) {
        if (workbook == null) {
            try {
                File fileDes = new File(excelFilePath);
                InputStream str = new FileInputStream(fileDes);
                workbook = new XSSFWorkbook(str);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    public static void writeExcelFile(String fileName) {
        File file = new File(fileName);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try (OutputStream fos = new FileOutputStream(fileName)) {
            workbook.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String getCellValue(int sheetIndex, int rowIndex, int columnIndex) {
        XSSFSheet sheetAt = workbook.getSheetAt(sheetIndex);
        XSSFRow row = sheetAt.getRow(rowIndex);
        XSSFCell cell = row.getCell(columnIndex);
        return getCellValue(cell);
    }

    public static void setCellValue(int sheetIndex, int rowIndex, int columnIndex, String cellValue) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        if (Objects.isNull(sheet)) {
            sheet = workbook.createSheet();
        }
        XSSFRow row = sheet.getRow(rowIndex);
        if (Objects.isNull(row)) {
            row = sheet.createRow(rowIndex);
        }
        XSSFCell cell = row.getCell(columnIndex);
        if (Objects.isNull(cell)) {
            cell = row.createCell(columnIndex);
        }
        cell.setCellValue(cellValue);
    }

    public static CellType getCellType(CellLocation cellLocation) {
        int sheetIndex = cellLocation.getSheetIndex();
        int rowIndex = cellLocation.getRowIndex();
        int colIndex = cellLocation.getColIndex();
        try {
            XSSFCell cell = workbook.getSheetAt(sheetIndex).getRow(rowIndex).getCell(colIndex);
            return cell.getCellType();
        } catch (Exception e) {
            return null;
        }
    }

    public static String getCellValue(XSSFCell cell) {
        String cellValue = StringUtils.EMPTY;
        if (Objects.nonNull(cell)) {
            CellType cellType = cell.getCellType();
            //判断单元格的数据类型
            switch (cellType) {
                case NUMERIC: // 数字
                    cellValue = new DecimalFormat("0.00").format(Double.valueOf(cell.getNumericCellValue()));
                    break;
                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    if (cellValue.equals("--")){
                        cellValue = new DecimalFormat("0.00").format(0);
                    }
                    break;
                case BOOLEAN: // Boolean
                    cellValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA: // 公式
                    cellValue = cell.getCellFormula();
                    cellValue = new DecimalFormat("0.00").format(Double.valueOf(cell.getNumericCellValue()));
                    break;
                case BLANK: // 空值
                    cellValue = StringUtils.EMPTY;
                    // todo
                    cellValue = "0.00";
                    break;
                case ERROR: // 故障
                    cellValue = "error";
                    break;
                default:
                    cellValue = "unkown";
                    break;
            }
        }
        return cellValue;
    }

}
